package com.white.algebra.dao.impl;

import com.white.algebra.dao.RoleDao;
import com.white.algebra.domain.Role;
import com.white.algebra.utils.JdbcUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class RoleDaoImpl implements RoleDao {
    private List<Role> getRoles(String id, String s) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        List<Role> roleList=null;
        String sql = s;
        Object[] params = { id};
        roleList= (List<Role>) qr.query(sql,params, new BeanListHandler(Role.class));
        return roleList;
    }

    /**
     * 查询出所有的角色
     *
     * @return
     */
    @Override
    public List<Role> findAll() throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        List<Role> roleList=null;
        String sql = "select * from role";
        roleList= (List<Role>) qr.query(sql,new BeanListHandler(Role.class));
        return roleList;
    }

    /**
     * 根据用户id查询出所对应的角色
     *
     * @param userId
     * @return
     * @throws Exception
     */
    @Override
    public List<Role> findRoleByUserId(String userId) throws Exception {
        return getRoles(userId, "select * from role where id in (select roleId from users_role where userId=?)");
    }

    /**
     * 根据权限id查询所对应的角色
     *
     * @param id
     * @return
     */
    @Override
    public List<Role> findRolesByPermissionId(String id) throws SQLException {
        return getRoles(id, "select * from role where id in(select roleId from role_permission where permissionId=?)");
    }

    /**
     * 根据角色id查询对应的角色与对应的资源权限
     *
     * @param id
     * @return
     */
    @Override
    public List<Role> findById(String id) throws SQLException {
        return getRoles(id, "select * from role where id=?");
    }

    /**
     * 查询用户没有的角色
     *
     * @param id
     * @return
     */
    @Override
    public List<Role> findNotRoleByUserId(String id) throws SQLException {
        return getRoles(id, "select * from role where id not in(select roleId from users_role where userId=?)");
    }

    /**
     * 查询权限没有的角色
     *
     * @param id
     * @return
     */
    @Override
    public List<Role> findRoleByPermissionId(String id) throws SQLException {
        return getRoles(id, "select * from role where id not in(select roleId from role_permission where permissionId=?)");
    }



    /**
     * 保存角色信息
     *
     * @param role
     */
    @Override
    public void save(Role role) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "insert into role(roleName,roleDesc) values(?,?)";
        Object[] params = {role.getRoleName(),role.getRoleDesc()};
        qr.update(sql,params);
    }

    /**
     * 根据角色id删除角色
     *
     * @param roleId
     */
    @Override
    public void deleteById(String roleId) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "delete from role where id=?";
        Object[] params = {roleId};
        qr.update(sql,params);
    }

    /**
     * 根据角色id删除所用户_角色关联表中的联系
     *
     * @param roleId
     */
    @Override
    public void deleteUserAndRoleById(String roleId) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "delete from users_role where roleId=?";
        Object[] params = {roleId};
        qr.update(sql,params);
    }

    /**
     * 根据角色id删除所角色_资源权限关联表中的联系
     *
     * @param roleId
     */
    @Override
    public void deleteRole_PermissionById(String roleId) throws SQLException {
        QueryRunner qr = new QueryRunner(JdbcUtil.getDataSource());
        String sql = "delete from users_role where roleId=?";
        Object[] params = {roleId};
        qr.update(sql,params);
    }
}
